package com.xiaowen.tool.excle;

import com.alibaba.fastjson.JSONArray;

import com.google.common.collect.Maps;
import com.xiaowen.tool.ObjUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * User: zhuminghua
 * Date: 2017/5/17 下午7:42
 * Desc:
 */
@Slf4j
public class PoiExcelUtils {


    /**
     * 创建excel标题样式
     *
     * @param wb
     * @return
     */
    public static CellStyle createTitleCellStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        // 左右对齐
        style.setAlignment(HorizontalAlignment.LEFT);
        // 垂直对齐
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
//        style.setBorderTop(BorderStyle.THIN);
//        style.setBorderBottom(BorderStyle.THIN);
//        style.setBorderLeft(BorderStyle.THIN);
//        style.setBorderRight(BorderStyle.THIN);
        // 设置边框颜色
//        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
//        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
//        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
//        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 设置背景色
//        style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
        // 设置前景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置字体
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 16);
        font.setItalic(false);//斜体
        font.setStrikeout(false);//删除线
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 创建excel普通样式
     *
     * @param wb
     * @return
     */
    public static CellStyle createNormalCellStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        // 左右对齐
        style.setAlignment(HorizontalAlignment.LEFT);
        // 垂直对齐
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 创建excel普通样式
     *
     * @param wb
     * @return
     */
    public static CellStyle createNumberCellStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        // 左右对齐
        style.setAlignment(HorizontalAlignment.RIGHT);
        // 垂直对齐
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置自动换行
        style.setWrapText(true);
        style.setDataFormat((short) 2);
        return style;
    }


    public static Workbook createExcel(List<String> titleAry, List<String[]> dataList) {
        Workbook workbook = new XSSFWorkbook();
        CreationHelper createHelper = workbook.getCreationHelper();
        Sheet sheet = workbook.createSheet("sheet1");
        //设置第一行样式
        CellStyle styleTitle = PoiExcelUtils.createTitleCellStyle(workbook);
        //设置普通样式
        CellStyle styleNormal = PoiExcelUtils.createNormalCellStyle(workbook);

        //创建第0行
        Row row0 = sheet.createRow(0);
        for (int i = 0; i < titleAry.size(); i++) {
            Cell cell = row0.createCell(i);
            cell.setCellValue(createHelper.createRichTextString(titleAry.get(i)));
            cell.setCellStyle(styleTitle);
            sheet.autoSizeColumn(i, true);
            sheet.setColumnWidth(i, 256 * 30);
        }

        //循环创建第每一行
        for (int i = 0; i < dataList.size(); i++) {
            //创建所需要的行数 因为数据从第1行开始
            Row row = sheet.createRow(i + 1);
            //遍历每个对象
            String[] strings = dataList.get(0);
            for (int i1 = 0; i1 < strings.length; i1++) {
                //设置每一行的值与样式
                Cell cell = row.createCell(i1);
                Object value = strings[i1];
                if (value instanceof BigDecimal){

                }else {
                    cell.setCellValue(createHelper.createRichTextString(ObjUtils.ifNull(strings[i1], "")));
                    cell.setCellStyle(styleNormal);
                }

            }
        }
        return workbook;
    }


    public static Workbook createExcel(List<ExcelExportDTO> exportDTOList) {
        if (CollectionUtils.isEmpty(exportDTOList)){
            return null;
        }
        Workbook workbook = new XSSFWorkbook();
        CreationHelper createHelper = workbook.getCreationHelper();
        int sheetNo = 1;
        for (ExcelExportDTO excelExportDTO : exportDTOList){
            List<ExcelTitle> titleAry = excelExportDTO.getTitleAry();
            List<Map<String, Object>> dataList = excelExportDTO.getDataList();
            Set<String> deDuplication = new HashSet<>();
            dataList.forEach(map -> {
                map.forEach((key, value) -> {
                    if (ObjUtils.isNotEmpty(value)) {
                        deDuplication.add(key);
                    }
                });
            });
            titleAry.removeIf(excelTitle -> excelTitle.isFlag() && !deDuplication.contains(excelTitle.getName()));

            Sheet sheet = workbook.createSheet("sheet"+ObjUtils.toString(sheetNo));
            //设置第一行样式
            CellStyle styleTitle = PoiExcelUtils.createTitleCellStyle(workbook);
            //设置普通样式
            CellStyle styleNormal = PoiExcelUtils.createNormalCellStyle(workbook);

            CellStyle styleNumber = PoiExcelUtils.createNumberCellStyle(workbook);
            //创建第0行
            Row row0 = sheet.createRow(0);
            for (int i = 0; i < titleAry.size(); i++) {
                Cell cell = row0.createCell(i);
                cell.setCellValue(createHelper.createRichTextString(titleAry.get(i).getName()));
                cell.setCellStyle(styleTitle);
                sheet.autoSizeColumn(i, true);
                sheet.setColumnWidth(i, 256 * 30);
            }

            //循环创建第每一行
            for (int i = 0; i < dataList.size(); i++) {
                //创建所需要的行数 因为数据从第1行开始
                Row row = sheet.createRow(i + 1);
                //遍历每个对象
                Map<String, Object> stringMap = dataList.get(i);
                for (int i1 = 0; i1 < titleAry.size(); i1++) {
                    //设置每一行的值与样式
                    Cell cell = row.createCell(i1);
                    Object s = stringMap.get(titleAry.get(i1).getName());
                    if (s instanceof BigDecimal){
                        cell.setCellValue(ObjUtils.toBigDecimal(s,BigDecimal.ZERO).doubleValue());
                        cell.setCellStyle(styleNumber);
                    } else {
                        cell.setCellValue(createHelper.createRichTextString(ObjUtils.toString(s,"")));
                        cell.setCellStyle(styleNormal);
                    }
                }
            }
            sheetNo++;
        }
        return workbook;
    }

}
